package dbConnections;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import dbStruct.Column;
import dbStruct.Connector;
import dbStruct.DatabaseStructure;
import dbStruct.Table;
import dbStruct.Connector.Cardinality;
import dbStruct.Connector.RelationType;

public abstract class DBConnection {
	protected static Map<Integer,String> 	map;
	protected String 				ConnectionString="";
	protected Connection			connection=null;;
	protected Statement				statement=null;;
	protected ResultSet				rs=null;;
	protected String				pwd="";
	protected String				user="";
	protected String				host="";
	protected String				dbPath="";
	protected String				dbClass = "";
	protected String				dbConnectionString = "";
	protected DatabaseStructure		dbStruct;
	protected int 					dbType;
	
	protected String				mySQLClass = "com.mysql.jdbc.Driver";
	protected String				msAccessClass = "sun.jdbc.odbc.JdbcOdbcDriver";
	protected String				mySQLConnection = "jdbc:mysql://localhost/";
	protected String				msAccessConnection = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
	
	public static final int mySQL    = 1;
	public static final int msAccess = 2;
	public static final int Oracle   = 3;
	
	public void setDbPath(String dbPath)
	{
		this.dbPath=dbPath;
	}
	
	public void setUser(String user)
	{
		this.user=user;
	}
	
	public void setPwd(String pwd)
	{
		this.pwd=pwd;
	}
	
	public DBConnection(int type)
	{
		super();
		this.dbType=type;
		this.dbStruct=DatabaseStructure.getInstance();
		getConnectionString(type);
		Connect();
	}
	
	protected void Connect()
	{
		try {
			Class.forName(dbClass).newInstance();
			System.out.println("CLASSFORNAME ESEGUITA");
		} catch (InstantiationException e) {
			System.out.println("ERRORE DURANTE L'ISTANZIAZIONE");
		} catch (IllegalAccessException e) {
			System.out.println("ERRORE DURANTE L'ACCESSO AL DATABASE");
		} catch (ClassNotFoundException e) {
			System.out.println("CLASSE NON TROVATA");
		}
		this.getJdbcTypeName();
	}
	
	public DatabaseStructure getStructure()
	{
		System.out.println("\n**********************************");
		System.out.println("** CREAZIONE STRUTTURA IN CORSO **");
		System.out.println("**********************************\n");
		getTables();
		getRelations();
		return this.dbStruct;
	}
	
	public Connection getConnection()
	{ 
		this.ConnectionString =  this.dbConnectionString + dbPath;        
		try 
		{
			this.connection = DriverManager.getConnection(ConnectionString,user,pwd);
		} catch (SQLException e) {
			System.out.println("ERRORE DURANTE LA CREAZIONE DELLA CONNESSIONE");
			e.printStackTrace();
		}
		getStructure();
		return connection;
	}
	
	public Connection getConnection(String dbPath)
	{
		this.dbPath = dbPath;
		getConnection();
		return connection;
	}
	
	public Connection getConnection(String dbPath, String user, String pwd)
	{
		this.dbPath=dbPath;
		this.user=user;
		this.pwd=pwd;
		getConnection();
		return connection;
	}
	
	private String getConnectionString(int type)
	{
		switch (type)
		{
		case mySQL: 
			this.dbType=type;
			this.dbConnectionString=this.mySQLConnection;
			this.dbClass=this.mySQLClass;
			break;
		case msAccess:
			this.dbType=type;
			this.dbConnectionString=this.msAccessConnection;
			this.dbClass=this.msAccessClass;
			break;
		}
		
		return this.dbConnectionString;
	}
	
	abstract protected void getRelations();
	
	protected void getTables()
	{
		ResultSet 	rs=null;
		
		try {
			rs = connection.getMetaData().getTables(null, null, null, null);
			
			while (rs.next())
			{
				//String catalog = rs.getString("TABLE_CAT");
				//String schema = rs.getString("TABLE_SCHEM");
				//String tipo = rs.getString("TABLE_TYPE").replace(" ", "_");
				String tableName = rs.getString("TABLE_NAME"); 
			  
				Table table = new Table(tableName);
				System.out.println("    INSERITA TABELLA " + table.getName());
				dbStruct.getNodes().add(table); //Aggiungo la tabella alla collezione NODES
				getColumns(table); //Leggo gli attributi della tabella.
				getIndexes(table); //Leggo gli indici della tabella.
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	protected void getColumns(Table table)
	{
		DatabaseMetaData db;
		try {
			db = connection.getMetaData();
			String tableName = table.getName();
			ResultSet rs = db.getColumns(null, null, tableName, "%");
			while(rs.next())
			{	//creo il nodo attributo
				String name  = tableName + "." + rs.getString("COLUMN_NAME");
				System.out.println("    TROVATA COLONNA " + name);
				int dataType = rs.getInt("DATA_TYPE");
				int columnSize = rs.getInt("COLUMN_SIZE");
				int ordinalPosition = rs.getInt("ORDINAL_POSITION");
				
				Column column = new Column(name); //Creo l'attributo
					column.setOrdinalPosition(ordinalPosition);
					column.setColumnType(dataType);
					column.setDim(columnSize);
				
				dbStruct.getNodes().add(column); //Aggiungo l'attributo alla collezione NODES
				//Creo la connessione tra i nodi
				Connector conn = new Connector(table, column, RelationType.ATTRIBUTE, Cardinality.ONE2ONE);
				//Aggiungo la connessione alla collezione CONNECTORS
				dbStruct.getConnectors().add(conn);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void getIndexes(Table table)
	{
		DatabaseMetaData db;
		System.out.println("\n\nINDEXES");
		try {
			db = connection.getMetaData();
			String tableName = table.getName();
			ResultSet indexList = db.getIndexInfo(null,null,tableName,false,false);
		    while(indexList.next())
		    {	//Prelevo tutti i dati necessari all'indice
	        	boolean pk=false;
	        	String  idxName = indexList.getString("INDEX_NAME");
	        	String	colName = indexList.getString("COLUMN_NAME");
	        	
	        	if(idxName != null && colName != null)
	        	{
	        		if (idxName.equalsIgnoreCase("PRIMARYKEY")||idxName.equalsIgnoreCase("PRIMARY"))
		        		pk=true;
		        	else
		        		pk=false;
	        		
	        		//prendo il nodo attributo e lo modifico.
	        		String columnName = tableName + "." + colName;
	        		Column column = (Column) dbStruct.getNodes().get(columnName);
	        		if(!column.isIndex())
	        		{
	        			 column.setIndex(true);
	        			 column.setPrimary(pk);
	        			 System.out.println("    NUOVO INDICE " + column.getName() + " INDICE PK " + column.isPrimary());
	        		}
	        	}
	        }
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public ResultSet getResultSet(String query)
	{
		try {
			statement = connection.createStatement();
			rs = statement.executeQuery(query);
			
		} catch (SQLException e) {
			System.out.println("ERRORE NELLA CREAZIONE DELLA QUERY");
		}
		return rs;
	}
	
	private void getJdbcTypeName() 
	{
	    // Get all field in java.sql.Types
		if(map==null)
		{
			map = new HashMap<Integer, String>();
			java.lang.reflect.Field[] fields = java.sql.Types.class.getFields();
		    for (int i = 0; i < fields.length; i++) {
		      try {
		        String name = fields[i].getName();
		        Integer value = (Integer) fields[i].get(null);
		        map.put(value, name);
		      } catch (IllegalAccessException e) {
		      }
		    }
		    System.out.println("MAPPA COMPLETA DEI TIPI JDBC:");
		    System.out.println(map);
		}
	  }
	
	public static String getTypeName(int type)
	{
		return map.get(type);
	}
	
	
}
